package com.personnel_management.dao;
import com.personnel_management.pojo.Staff;
import com.personnel_management.pojo.StaffVo;
import org.apache.ibatis.annotations.*;
import org.springframework.stereotype.Repository;
import java.util.List;
@Repository
@Mapper
public interface StaffDao {
    //添加员工
    /*
    INSERT INTO staff(name,phone,sex,birthday,edu,depart_id,position_id) VALUES
    ("刘华文","13312349999",0,"2020-03-15","衡水学院",2,1);
    */
    @Insert("INSERT INTO staff (name,phone,sex,birthday,edu,depart_id,position_id) VALUES\n" +
            " (#{name},#{phone},#{sex},#{birthday},#{edu},#{depart_id},#{position_id});")
    public Integer addStaff(Staff staff);
    //删除员工(通过员工id) DELETE FROM staff WHERE id=3;
    @Delete("DELETE FROM staff WHERE id=#{id}")
    public Integer deleteStaff(Integer id);
    //修改员工(修改姓名、电话、性别、生日、毕业院校、部门编号、职务编号)
    @UpdateProvider(type=setStaffSql.class,method = "getSql")
    public Integer setStaff(Staff staff);
    //特别注意查询结果有三个name,sql语句中需要用as 起别名 depart_name position_name
    //查询所有员工(多表查询) 显示出员工表的所有信息，部门表的名称、城市，职务表的职务名称、工资
    /*
    SELECT staff.name,staff.phone,staff.sex,staff.birthday,staff.edu,depart.name AS depart_name,depart.city,position.name AS position_name,position.wages
    FROM staff,depart,position
    WHERE staff.depart_id=depart.id
    AND staff.position_id=position.id;
    */
    public String selectSQL="SELECT staff.id,staff.name,staff.phone,staff.sex,staff.birthday,staff.edu,depart.name AS depart_name,depart.city,position.name AS position_name,position.wages  \n" +
            "FROM staff,depart,position\n" +
            "WHERE staff.depart_id=depart.id\n" +
            "AND staff.position_id=position.id";
    @Select(selectSQL)
    public List<StaffVo> selectAllStaffs();
    //查询员工(按照姓名、手机号、毕业院校、部门名称、职位、性别)
    //SELECT * FROM staff,position WHERE staff.position_id=position.id AND position.name="设计师";
    @SelectProvider(type=findStaffSql.class,method = "getSql")
    public List<StaffVo> selectStaffs(StaffVo staff);
    /*
    public String test(String str)
    {
        //需求:删掉末尾的逗号(如果有的话)
        if(str.endsWith(","))
            return str.substring(0,str.length()-1);
        else
            return str;
    }
    */
    //修改员工信息的SQL
    class setStaffSql{
        public String getSql(Staff staff)
        {
            //修改职务编号、部门编号功能暂未实现
            //需要去掉末尾的,(如果有的话)
            StringBuilder builder=new StringBuilder("UPDATE staff SET");
            if(staff.getSex()!=null)
                builder.append(" sex=#{sex},");
            if(staff.getPhone()!=null)
                builder.append(" phone=#{phone},");
            if(staff.getName()!=null)
                builder.append(" name=#{name},");
            if(staff.getEdu()!=null)
                builder.append(" edu=#{edu},");
            if(staff.getBirthday()!=null)
                builder.append(" birthday=#{birthday} ,");
            String s = builder.toString();
            if(s.endsWith(",")) s= s.substring(0,s.length()-1);
            return s+" WHERE id=#{id}";
        }
    }
    //按条件查询员工信息的SQL
    class findStaffSql{
        private StringBuilder builder=new StringBuilder(selectSQL);
        public String getSql(StaffVo staff)
        {
            //需要查询的条件拼接到后面即可
            if(staff.getSex()!=null)
                builder.append(" AND staff.sex=#{sex}");
            if(staff.getBirthday()!=null)
                builder.append(" AND staff.birthday=#{birthday}");
            if(staff.getEdu()!=null)
                builder.append(" AND staff.edu=#{edu}");
            if(staff.getName()!=null)
                builder.append(" AND staff.name=#{name}");
            if(staff.getPhone()!=null)
                builder.append(" AND staff.phone=#{phone}");
            if(staff.getDepart_name()!=null)
                builder.append(" AND depart.name=#{depart_name}");
            if(staff.getPosition_name()!=null)
                builder.append(" AND position.name=#{position_name}");
            return builder.toString();
        }
    }

    //根据手机号查找是否查找改用户
    //SELECT COUNT(*)>0 FROM staff WHERE phone="13312341234";
    @Select("SELECT COUNT(*)>0 FROM staff WHERE phone= #{phone}")
    public Boolean findStaff(String phone);
}
